USE [ACPTest]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_questions_exams]') AND parent_object_id = OBJECT_ID(N'[dbo].[questions]'))
ALTER TABLE [dbo].[questions] DROP CONSTRAINT [FK_questions_exams]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__questions__optio__15502E78]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[questions] DROP CONSTRAINT [DF__questions__optio__15502E78]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__questions__optio__164452B1]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[questions] DROP CONSTRAINT [DF__questions__optio__164452B1]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__questions__optio__173876EA]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[questions] DROP CONSTRAINT [DF__questions__optio__173876EA]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__questions__optio__182C9B23]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[questions] DROP CONSTRAINT [DF__questions__optio__182C9B23]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__questions__optio__1920BF5C]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[questions] DROP CONSTRAINT [DF__questions__optio__1920BF5C]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_questions_status]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[questions] DROP CONSTRAINT [DF_questions_status]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__questions__uploa__1B0907CE]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[questions] DROP CONSTRAINT [DF__questions__uploa__1B0907CE]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__questions__descr__1BFD2C07]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[questions] DROP CONSTRAINT [DF__questions__descr__1BFD2C07]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__questions__compl__1CF15040]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[questions] DROP CONSTRAINT [DF__questions__compl__1CF15040]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__questions__issam__1DE57479]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[questions] DROP CONSTRAINT [DF__questions__issam__1DE57479]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__questions__quest__1ED998B2]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[questions] DROP CONSTRAINT [DF__questions__quest__1ED998B2]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__questions__quest__1FCDBCEB]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[questions] DROP CONSTRAINT [DF__questions__quest__1FCDBCEB]
END

GO

USE [ACPTest]
GO

/****** Object:  Table [dbo].[questions]    Script Date: 04/29/2012 11:32:39 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[questions]') AND type in (N'U'))
DROP TABLE [dbo].[questions]
GO

USE [ACPTest]
GO

/****** Object:  Table [dbo].[questions]    Script Date: 04/29/2012 11:32:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[questions](
	[questionid] [int] IDENTITY(1,1) NOT NULL,
	[examid] [int] NOT NULL,
	[questiontype] [varchar](45) NOT NULL,
	[question] [varchar](2000) NOT NULL,
	[option1] [varchar](2000) NULL,
	[option2] [varchar](2000) NULL,
	[option3] [varchar](2000) NULL,
	[option4] [varchar](2000) NULL,
	[option5] [varchar](2000) NULL,
	[status] [bit] NOT NULL,
	[answer] [varchar](2000) NOT NULL,
	[uploadedby] [varchar](45) NULL,
	[uploadeddate] [datetime] NOT NULL,
	[referenceid] [int] NOT NULL,
	[description] [varchar](2000) NULL,
	[complexity] [varchar](45) NULL,
	[issample] [bit] NOT NULL,
	[questionimage1] [varchar](45) NULL,
	[questionimage2] [varchar](45) NULL,
	[topicId] [int] NULL,
 CONSTRAINT [PK_questions] PRIMARY KEY CLUSTERED 
(
	[questionid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[questions]  WITH NOCHECK ADD  CONSTRAINT [FK_questions_exams] FOREIGN KEY([examid])
REFERENCES [dbo].[exams] ([examid])
GO

ALTER TABLE [dbo].[questions] NOCHECK CONSTRAINT [FK_questions_exams]
GO

ALTER TABLE [dbo].[questions] ADD  DEFAULT (NULL) FOR [option1]
GO

ALTER TABLE [dbo].[questions] ADD  DEFAULT (NULL) FOR [option2]
GO

ALTER TABLE [dbo].[questions] ADD  DEFAULT (NULL) FOR [option3]
GO

ALTER TABLE [dbo].[questions] ADD  DEFAULT (NULL) FOR [option4]
GO

ALTER TABLE [dbo].[questions] ADD  DEFAULT (NULL) FOR [option5]
GO

ALTER TABLE [dbo].[questions] ADD  CONSTRAINT [DF_questions_status]  DEFAULT ((1)) FOR [status]
GO

ALTER TABLE [dbo].[questions] ADD  DEFAULT (NULL) FOR [uploadedby]
GO

ALTER TABLE [dbo].[questions] ADD  DEFAULT (NULL) FOR [description]
GO

ALTER TABLE [dbo].[questions] ADD  DEFAULT (NULL) FOR [complexity]
GO

ALTER TABLE [dbo].[questions] ADD  DEFAULT ('0') FOR [issample]
GO

ALTER TABLE [dbo].[questions] ADD  DEFAULT (NULL) FOR [questionimage1]
GO

ALTER TABLE [dbo].[questions] ADD  DEFAULT (NULL) FOR [questionimage2]
GO

-------------------------------------------------
---Articles Tabel

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Articles_Articles]') AND parent_object_id = OBJECT_ID(N'[dbo].[Articles]'))
ALTER TABLE [dbo].[Articles] DROP CONSTRAINT [FK_Articles_Articles]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Articles_Exams]') AND parent_object_id = OBJECT_ID(N'[dbo].[Articles]'))
ALTER TABLE [dbo].[Articles] DROP CONSTRAINT [FK_Articles_Exams]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Articles_ParentArticleID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Articles] DROP CONSTRAINT [DF_Articles_ParentArticleID]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Articles_Status]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Articles] DROP CONSTRAINT [DF_Articles_Status]
END

GO

USE [ACPTest]
GO

/****** Object:  Table [dbo].[Articles]    Script Date: 04/29/2012 11:32:56 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Articles]') AND type in (N'U'))
DROP TABLE [dbo].[Articles]
GO

USE [ACPTest]
GO

/****** Object:  Table [dbo].[Articles]    Script Date: 04/29/2012 11:32:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Articles](
	[ArticleID] [int] IDENTITY(1,1) NOT NULL,
	[ExamID] [int] NOT NULL,
	[ArticleTitle] [varchar](100) NULL,
	[ArticleDescription] [varchar](max) NULL,
	[ParentArticleID] [int] NULL,
	[Position] [int] NULL,
	[Keywords] [varchar](200) NULL,
	[Status] [bit] NULL,
 CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED 
(
	[ArticleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Foreign Key to Self - ArticleID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Articles', @level2type=N'COLUMN',@level2name=N'ParentArticleID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Used to decide the display position' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Articles', @level2type=N'COLUMN',@level2name=N'Position'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Should be in HTML format' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Articles', @level2type=N'COLUMN',@level2name=N'Keywords'
GO

ALTER TABLE [dbo].[Articles]  WITH CHECK ADD  CONSTRAINT [FK_Articles_Articles] FOREIGN KEY([ParentArticleID])
REFERENCES [dbo].[Articles] ([ArticleID])
GO

ALTER TABLE [dbo].[Articles] CHECK CONSTRAINT [FK_Articles_Articles]
GO

ALTER TABLE [dbo].[Articles]  WITH CHECK ADD  CONSTRAINT [FK_Articles_Exams] FOREIGN KEY([ExamID])
REFERENCES [dbo].[exams] ([examid])
GO

ALTER TABLE [dbo].[Articles] CHECK CONSTRAINT [FK_Articles_Exams]
GO

ALTER TABLE [dbo].[Articles] ADD  CONSTRAINT [DF_Articles_ParentArticleID]  DEFAULT ((0)) FOR [ParentArticleID]
GO

ALTER TABLE [dbo].[Articles] ADD  CONSTRAINT [DF_Articles_Status]  DEFAULT ((1)) FOR [Status]
GO

-------------------------------------------------
---Topics Tabel
USE [ACPTest]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Topics_exams]') AND parent_object_id = OBJECT_ID(N'[dbo].[Topics]'))
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT [FK_Topics_exams]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Topics_Topics]') AND parent_object_id = OBJECT_ID(N'[dbo].[Topics]'))
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT [FK_Topics_Topics]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Topics_status]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT [DF_Topics_status]
END

GO

USE [ACPTest]
GO

/****** Object:  Table [dbo].[Topics]    Script Date: 04/29/2012 11:33:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Topics]') AND type in (N'U'))
DROP TABLE [dbo].[Topics]
GO

USE [ACPTest]
GO

/****** Object:  Table [dbo].[Topics]    Script Date: 04/29/2012 11:33:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Topics](
	[TopicID] [int] IDENTITY(1,1) NOT NULL,
	[TopicTitle] [varchar](100) NOT NULL,
	[TopicDescription] [varchar](1000) NULL,
	[ParentTopicID] [int] NULL,
	[examid] [int] NOT NULL,
	[status] [bit] NOT NULL,
 CONSTRAINT [PK_Topics] PRIMARY KEY CLUSTERED 
(
	[TopicID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Topics]  WITH CHECK ADD  CONSTRAINT [FK_Topics_exams] FOREIGN KEY([examid])
REFERENCES [dbo].[exams] ([examid])
GO

ALTER TABLE [dbo].[Topics] CHECK CONSTRAINT [FK_Topics_exams]
GO

ALTER TABLE [dbo].[Topics]  WITH CHECK ADD  CONSTRAINT [FK_Topics_Topics] FOREIGN KEY([ParentTopicID])
REFERENCES [dbo].[Topics] ([TopicID])
GO

ALTER TABLE [dbo].[Topics] CHECK CONSTRAINT [FK_Topics_Topics]
GO

ALTER TABLE [dbo].[Topics] ADD  CONSTRAINT [DF_Topics_status]  DEFAULT ((1)) FOR [status]
GO

